﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_TOUR_Select_Paging')
	BEGIN
		DROP Procedure usp_UPDMS_REF_TOUR_Select_Paging
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_TOUR_Select_Paging
**	Desc : 기행기를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_REF_TOUR_Select_Paging 0, 20, '', '','2004-01-01','2013-05-20','','5.0','','',0
**	Called by : Ref_Dac_UPDMS_REF_TOUR.cs
**	Program ID : Ref1012m
**	Auth : 송시명
**	Date : 2013-04-17
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_TOUR_Select_Paging]
@li_st_page int,
@li_ed_page int,
@ls_shop_id nvarchar(max),
@ls_nick_nm nvarchar(200),
@ls_tour_dt_from nvarchar(10),
@ls_tour_dt_to nvarchar(10),
@ls_homerun_yn nvarchar(5),
@ls_satisfaction nvarchar(3),
@ls_contents nvarchar(max),
@ls_keyword nvarchar(2000),
@ls_file_exist varchar(1)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @shop_ids nvarchar(max)
IF @ls_shop_id = ''
BEGIN
    SET @shop_ids = '%'
END
ELSE
BEGIN
    SET @shop_ids = @ls_shop_id
END

--첨부파일존재여부
DECLARE @file_exist int
IF @ls_file_exist = ''
BEGIN
    SET @file_exist = 0
END
ELSE
BEGIN
    SET @file_exist = 1
END

SELECT a.Seq,
       a.Shop_Id,
       a.Shop_Nm,
       a.Nick_Nm,
       a.Running_Time,
       a.Tour_Dt,
       a.Tour_Hour,
       a.Tour_Minute,
       a.Tour_Time,
       a.Place,
       a.Price,
       a.HomeRun_Yn,
       a.HomeRun_Yn_Icon,
       a.Satisfaction,
       a.Contents,
       a.Contents_Word_Count,
       a.Keyword_Word_Count,
       a.Keyword,
       a.NaverMap_Link,
       a.Latitude,
       a.Longitude,
       a.Attach_File_Info
  FROM (
       SELECT ROW_NUMBER() OVER(ORDER BY urt.Tour_Dt DESC, urt.Tour_Hour DESC) AS Rnum,
              urt.Seq,
              urt.Shop_Id,
              urs.Shop_Nm,
              urt.Nick_Nm,
              urt.Running_Time,
              urt.Tour_Dt,
              urt.Tour_Hour,
              urt.Tour_Minute,
              urt.Tour_Hour + ':' + urt.Tour_Minute + ' ~ ' + CONVERT(CHAR(5), DATEADD(MI, Running_Time, CONVERT(DATETIME, urt.Tour_Dt + ' ' + urt.Tour_Hour + ':' + urt.Tour_Minute)), 8) AS Tour_Time,
              urt.Place,
              urt.Price,
              urt.HomeRun_Yn,
              CASE urt.HomeRun_Yn WHEN 'Y' THEN '<span class="sicon_heart"></span>'
                                  ELSE ''
              END HomeRun_Yn_Icon,
              urt.Satisfaction,
              urt.Contents,
              LEN(urt.Contents) AS Contents_Word_Count,
              urt.Keyword,
              LEN(urt.Keyword) AS Keyword_Word_Count,
              CASE urt.Latitude + urt.Longitude
                   WHEN '' THEN ''
                   ELSE '<div title="네이버맵" class="sbtn sbtn_map" onclick="goNaverMap(''' + urt.Latitude + ''',''' + urt.Longitude + ''');" />'
              END NaverMap_Link,
              urt.Latitude,
              urt.Longitude,
              CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File_Image(urt.Seq, 'Ref1012m') <= 0 THEN ''
                   ELSE '<div title="첨부파일" class="sbtn sbtn_fdisk" onclick="popupMultiImage(''' + CONVERT(VARCHAR, urt.Seq) + ''',''Ref1012m'');"></div>'
              END Attach_File_Info,
              CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File(urt.Seq, 'Ref1012m') <= 0 THEN 1
                   ELSE 0
              END Attach_File_Exist
         FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
         JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
        WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
              OR urt.Shop_Id LIKE @shop_ids
              )
          AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
          AND urt.Tour_Dt >= @ls_tour_dt_from
          AND urt.Tour_Dt <= @ls_tour_dt_to
          AND urt.HomeRun_Yn LIKE @ls_homerun_yn + '%'
          AND urt.Satisfaction >= @ls_satisfaction
          AND urt.Contents LIKE '%' + @ls_contents + '%'
          AND urt.Keyword LIKE '%' + @ls_keyword + '%'
          AND dbo.ufn_UPDMS_Get_Exist_Attach_File(urt.Seq, 'Ref1012m') >= @file_exist
       ) a
 WHERE a.Rnum > @li_st_page AND a.Rnum <= @li_ed_page

GO